/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.session;

import com.thienloc.omap.controller.scatchcard.PartnerProduction;
import com.thienloc.omap.controller.scatchcard.PartnerRevenue;
import com.thienloc.omap.controller.scatchcard.TransactionQueryObject;
import com.thienloc.omap.controller.scatchcard.TransactionResult;
import com.thienloc.omap.entity.Transaction;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author GiangLT
 */
@Stateless
public class TransactionFacade extends AbstractFacade<Transaction> {

    private final Logger logger = LoggerFactory.getLogger(this.getClass());
    @PersistenceContext(unitName = "omapPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public TransactionFacade() {
        super(Transaction.class);
    }

    public List<Transaction> find(TransactionQueryObject queryObject) {
        try {
            StringBuilder sql = new StringBuilder(" SELECT t FROM Transaction t WHERE t.lastUpdatedTime BETWEEN :startTime AND :endTime ");
            if (queryObject.getAccId() != null && !queryObject.getAccId().isEmpty()
                    && !queryObject.getAccId().equals("ALL")) {
                sql.append(" AND t.accId.partnerId = :accId ");
            }
            if (queryObject.getCardPin() != null && !queryObject.getCardPin().isEmpty()) {
                sql.append(" AND t.cardPin LIKE :cardPin ");
            }
            if (queryObject.getCardSerial() != null && !queryObject.getCardSerial().isEmpty()) {
                sql.append(" AND t.cardSerial LIKE :cardSerial ");
            }
            if (queryObject.getTransError() != null && !queryObject.getTransError().isEmpty()) {
                sql.append(" AND t.transErr LIKE :transErr ");
            }
            if (queryObject.getTransErrorDetail() != null && !queryObject.getTransErrorDetail().isEmpty()) {
                sql.append(" AND t.transErrDetail LIKE :transErrDetail ");
            }
            if (queryObject.getTransId() != null && !queryObject.getTransId().isEmpty()) {
                sql.append(" AND t.transactionPK.transId LIKE :transId ");
            }
            if (queryObject.getTransType() != null && !queryObject.getTransType().isEmpty()) {
                sql.append(" AND t.transType = :transType ");
            }
            if (queryObject.getAmountMax() != null && queryObject.getAmountMin() != null
                    && !queryObject.getAmountMax().isEmpty() && !queryObject.getAmountMin().isEmpty()) {
                sql.append(" AND t.amount BETWEEN :amountMin AND :amountMax ");
            }

            TypedQuery<Transaction> q = em.createQuery(sql.toString(), Transaction.class);
            q.setParameter("startTime", queryObject.getStartTime());
            q.setParameter("endTime", queryObject.getEndTime());
            if (queryObject.getAccId() != null && !queryObject.getAccId().isEmpty()
                    && !queryObject.getAccId().equals("ALL")) {
                q.setParameter("accId", queryObject.getAccId());
            }
            if (queryObject.getCardPin() != null && !queryObject.getCardPin().isEmpty()) {
                q.setParameter("cardPin", "%" + queryObject.getCardPin() + "%");
            }
            if (queryObject.getCardSerial() != null && !queryObject.getCardSerial().isEmpty()) {
                q.setParameter("cardSerial", "%" + queryObject.getCardSerial() + "%");
            }
            if (queryObject.getTransError() != null && !queryObject.getTransError().isEmpty()) {
                q.setParameter("transErr", "%" + queryObject.getTransError() + "%");
            }
            if (queryObject.getTransErrorDetail() != null && !queryObject.getTransErrorDetail().isEmpty()) {
                q.setParameter("transErrDetail", "%" + queryObject.getTransErrorDetail() + "%");
            }
            if (queryObject.getTransId() != null && !queryObject.getTransId().isEmpty()) {
                q.setParameter("transId", "%" + queryObject.getTransId() + "%");
            }
            if (queryObject.getTransType() != null && !queryObject.getTransType().isEmpty()) {
                q.setParameter("transType", queryObject.getTransType());
            }
            if (queryObject.getAmountMax() != null && queryObject.getAmountMin() != null
                    && !queryObject.getAmountMax().isEmpty() && !queryObject.getAmountMin().isEmpty()) {
                q.setParameter("amountMin", queryObject.getAmountMin());
                q.setParameter("amountMax", queryObject.getAmountMax());

            }
            return q.getResultList();
        } catch (NoResultException ex) {
            return new ArrayList<Transaction>(0);
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<PartnerProduction> find(String partnerId, Date startTime, Date endTime, String startWindow, String endWindow) {
        try {
            StringBuilder sql =
                    new StringBuilder(
                    " select to_char(t.last_updated_time, 'yyyy-mm-dd'), sum(to_number(t.amount)), count(t.trans_id), p.company_name "
                    + " from transaction t "
                    + " inner join partners p on p.partner_id = t.acc_id "
                    + " where t.last_updated_time between ? and ? "
                    + " and p.partner_id = ? "
                    + " AND to_char(t.last_updated_time, 'hh24:mi:ss') between ? and ? "
                    + " group by to_char(t.last_updated_time, 'yyyy-mm-dd'), p.company_name "
                    + " order by to_char(t.last_updated_time, 'yyyy-mm-dd') ");

            Query q = em.createNativeQuery(sql.toString());
            q.setParameter("1", startTime);
            q.setParameter("2", endTime);
            q.setParameter("3", partnerId);
            q.setParameter("4", startWindow);
            q.setParameter("5", endWindow);

            List resultList = q.getResultList();
            List<PartnerProduction> productions = new ArrayList<PartnerProduction>(resultList.size());
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                PartnerProduction production = new PartnerProduction();
                production.setDayStr(fields[0].toString());
                production.setPartnerName(fields[3].toString());
                production.setTotalAmount(fields[1] != null ? ((BigDecimal) fields[1]).floatValue() : 0);
                production.setTotalTransactions(fields[2] != null ? ((BigDecimal) fields[2]).intValue() : 0);

                productions.add(production);
            }
            return productions;
        } catch (NoResultException ex) {
            return new ArrayList<PartnerProduction>(0);
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }

    }

    public List<TransactionResult> getTransactionResultReport(String partnerId, Date startTime, Date endTime, String startWindow, String endWindow) {
        try {
            StringBuilder sql =
                    new StringBuilder(
                    " select count(*), trans_err, trans_err_detail from transaction "
                    + " where LAST_UPDATED_TIME BETWEEN ? and ? "
                    + " and acc_id = ? "
                    + " AND to_char(last_updated_time, 'hh24:mi:ss') between ? and ? "
                    + " group by trans_err, trans_err_detail "
                    + " order by trans_err, trans_err_detail ");

            Query q = em.createNativeQuery(sql.toString());
            q.setParameter("1", startTime);
            q.setParameter("2", endTime);
            q.setParameter("3", partnerId);
            q.setParameter("4", startWindow);
            q.setParameter("5", endWindow);

            List resultList = q.getResultList();
            List<TransactionResult> transactionResults = new ArrayList<TransactionResult>(resultList.size());
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                TransactionResult transactionResult = new TransactionResult();
                transactionResult.setCount(fields[0] == null ? 0 : ((BigDecimal) fields[0]).intValue());
                transactionResult.setTransErr(fields[1] == null ? "" : fields[1].toString());
                transactionResult.setTransErrDetail(fields[2] == null ? "" : fields[2].toString());
                transactionResult.setPartnerName(partnerId);

                transactionResults.add(transactionResult);
            }
            return transactionResults;
        } catch (NoResultException ex) {
            return new ArrayList<TransactionResult>(0);
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }

    public List<PartnerRevenue> getParnerRevenueReport(String partnerId, Date startTime, Date endTime, String startWindow, String endWindow) {
        try {
            StringBuilder sql =
                    new StringBuilder(
                    " select count(*), amount from transaction "
                    + " where LAST_UPDATED_TIME BETWEEN ? and ? "
                    + " and trans_err = '01' "
                    + " and acc_id = ? "
                    + " AND to_char(last_updated_time, 'hh24:mi:ss') between ? and ? "
                    + " group by amount "
                    + " order by count(*) desc ");

            Query q = em.createNativeQuery(sql.toString());
            q.setParameter("1", startTime);
            q.setParameter("2", endTime);
            q.setParameter("3", partnerId);
            q.setParameter("4", startWindow);
            q.setParameter("5", endWindow);

            List resultList = q.getResultList();
            List<PartnerRevenue> partnerRevenues = new ArrayList<PartnerRevenue>(resultList.size());
            for (Object row : resultList) {
                Object[] fields = (Object[]) row;
                PartnerRevenue partnerRevenue = new PartnerRevenue();
                partnerRevenue.setCount(fields[0] == null ? 0 : ((BigDecimal) fields[0]).intValue());
                partnerRevenue.setAmount(fields[1] == null ? -1 : Integer.parseInt(fields[1].toString()));
                partnerRevenue.setTotalAmount((long) (partnerRevenue.getAmount() * partnerRevenue.getCount()));
                partnerRevenue.setParnerName(partnerId);

                partnerRevenues.add(partnerRevenue);
            }
            return partnerRevenues;
        } catch (NoResultException ex) {
            return new ArrayList<PartnerRevenue>(0);
        } catch (Exception ex) {
            logger.error("", ex);
            return null;
        }
    }
}
